#region Imported Namespaces
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;

using BO = PHSRAG.Insight21.BusinessObjects;
using DataAccess = PHSRAG.Utility.DataAccess;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using SearchCriteria = PHSRAG.Insight21.SearchContext.SearchCriteria;
using Utility=PHSRAG.Utility;
#endregion

namespace PHSRAG.Insight21.COI
{
	///<summary>	
	/// The COIUserSummary object will act be a collection object supporting the following pages .
	/// 1.Supervisor Summary
	/// 2.Hierarchy Maintenance
	/// 3.Add COI Users
	/// 4.Reporting Pages
	/// 	4.a. Standard Reports
	/// 	4.b. custom Reports.
	/// Unlike other Insight Objects,this objects supports search,sort,loading of the above pages without caching the whole of the data(but the Ids for list pages).
	/// </summary>
	public class COIUserSummary: BO.BusinessObject
	{
		#region Constants
		/// <summary>unique key to cache the COIUserSummary Object(At any time the object will contain only the list of Ids(coiIds or personIds depending on the page invoking it)).</summary>
		private const string	COIUserSummaryKey = "COIUserSummary_7D2B7167-7C3A-4797-B9CA-8BFBE9B85084";
		/// <summary>Represents whether or not the sortcolumn should be sorted in the ascending order.</summary>
		private const bool	AscendingSortSequence = true;
		/// <summary>The following constant(IdDataTableName)represent the datatable consisting the list of Ids(obtained passing the search criteria).</summary>
		private const string	IdDataTableName = "IdList";

		/// <summary>
		/// Following are the Constants specifying the sort order(Asc and Desc)to be used in the stored procedure.
		/// </summary>
		private const string SortSequenceAscending="ASC";
		private const string SortSequenceDescending="DESC";		
		#endregion

		#region Instance Variables
		/// <summary>
		/// Sort order -Whether or not the sortcolumn should be sorted in ascending.
		/// </summary>
		private bool	sortAscending = AscendingSortSequence;
		/// <summary>
		/// SortColumn -represents the column to be sorted(Default sortcolumn ="LastName")
		/// </summary>
		private string	sortColumn = "LastName";
		/// <summary>
		/// DataTable to cache the selected admPersonIds
		/// </summary>
		private DataTable selectedIdsTable=null;
		//holds the admpersonId to be impersonated.
		private int idToImpersonate=0;
		#endregion

		#region Constructor
		/// <summary>
		/// Constructs a new COIUserSummary object based on the Cache Policy passed in as a parameter. 
		/// All the instance variables will be set to default values of their associated types.
		/// </summary>
		/// <param name="cachePolicy">available cachepolicy</param>
		public COIUserSummary(ICachePolicy cachePolicy) : base(cachePolicy)
		{
		}
		#endregion

		#region Public Properties
		/// <summary>
		/// Gets the total number of records in the data container.
		/// </summary>
		public int Count
		{
			get{return ( null == DataContainer )? 0 :DataContainer.Tables[0].Rows.Count;}
		}
		/// <summary>
		/// Gets/Sets the Sort ci=olumn name.
		/// </summary>
		public string SortColumn
		{
			get { return sortColumn; }
			set {sortColumn=value;}
		}		
		/// <summary>
		/// Gets/Sets the sort order.
		/// </summary>
		public bool SortAscending
		{
			get { return sortAscending; }
			set {sortAscending=value;}
		}
		#endregion

		#region caching
		/// <summary>
		/// Gets the key to be used in all caching operations for this instance of the object.
		/// </summary>
		public static string CacheKey	{ get { return COIUserSummaryKey; } }

		/// <summary>
		/// Cache the current instance using the prevalent cache policy.
		/// </summary>
		public override void Cache()
		{
			CachePolicy.Cache(CacheKey, this);
		}

		/// <summary>
		/// Remove the current instance from the cache using the prevalent cache policy.
		/// </summary>
		public override void UnCache()
		{
			CachePolicy.UnCache(CacheKey);
		}
		#endregion

		#region private methods
		/// <summary>
		/// This method converts the delimitted string to a dataset 
		/// Note: The converted string(dataset) can then be used to generate the xml 
		/// </summary>
		/// <param name="searchCriteria">Search criteria</param>
		/// <returns>Dataset</returns>
		private DataSet ConvertToDataSet(string delimittedString)//SearchCriteria searchCriteria)
		{
			DataSet ds=null;
			try
			{
				if ((delimittedString !=null) && (delimittedString != string.Empty))
				{
					string[] selectedList= delimittedString.Split(',');
					ds=new DataSet();
					DataTable dt=new DataTable();
					dt.Columns.Add("Col1");

					foreach (string str in selectedList)
					{
						DataRow dr=dt.NewRow();
						dr["Col1"]=str.Trim().Substring(1,str.Length-2);
						dt.Rows.Add(dr);
					}
					if (dt.Rows.Count>0)
					{
						ds.Tables.Add(dt);
					}				
				}
				return ds;
			}
			catch(Exception ex)
			{
				Utility.ExceptionReport.WriteToEventLog ("Failed to convert the delimitted string of selected states to Dataset,Error occurred while executing the ConvertToDataSet()", ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}			
		}
		#endregion

		#region Public methods

		/// <summary>
		/// Load the COI User Summary for the logged in user.
		/// </summary>
		/// <param name="connectionString">Database connection string</param>
		/// <param name="args">not implemented</param>
		public override void Load(string connectionString, params object[] args)
		{
			if (args == null && args.Length != 3 )
			{
				throw new Exception("COI User Summary load() requires a admpersonId and report type");
			}
			else
			{
				
				DataContainer = DataAccess.GetDataSet(connectionString, ((bool)args[3]==false)? "GetStandardReport":"GetStandardReportForSuperUser",
					DataAccess.BuildSqlParameterArray("@admPersonId", SqlDbType.Int, (int)args[0]
					,"@reportType",SqlDbType.VarChar,(string)args[2]
					));

				if ( DataContainer.Tables.Count == 1 )
					DataContainer.Tables[0].TableName = IdDataTableName;				
			}
			
		}

		/// <summary>
		/// This load method will be called passing the cached COI Search criteria and the userContext via the Params Object[] parameter.
		/// Unlike other objects this object will cache only the ids of the search result.These Ids inturn will support the loading,paging and sorting in the grid.
		/// </summary>
		/// <param name="connectionString">Database Connection string</param>
		/// <param name="searchCriteria">COI Search criteria</param>
		/// <param name="args">AdmPersonId((int)arg[0] and refering page((int)arg[1])</param>
		/// Note:
		/// Switch (int)arg[1]:
		///	case 0-->Supervisor Summary Page
		///	case 1-->Hierarchy maintenence page
		///   case=2-->Add COI users page
		public void Load(string connectionString, SearchCriteria searchCriteria, params object[] args)
		{
			try
			{
				if (args.Length < 2)
				{
					throw new Exception("COI User Summary load() requires a admpersonId and refernce page index");
				}
				else if(searchCriteria != null) 
				{
					string sortCriteria=string.Format("{0} {1}", SortColumn, sortAscending ? SortSequenceAscending : SortSequenceDescending);
					
					DataSet ds=ConvertToDataSet(searchCriteria[Constants.COISearch.Status]);

					switch  ((int)args[1])
					{
						case (int)Constants.SourcePage.SupervisorSummarypage://Supervisor summary page call(Gets all the descendants down the hierarchy for supervisors and all the users in the system for Super user privilege)
                            if ((bool)args[2])
                            {
                                DataContainer = DataAccess.GetDataSet(connectionString, "GetCOIUserHierarchyForSuperUser",
                                    DataAccess.BuildSqlParameterArray("@admPersonId", SqlDbType.Int, (int)args[0]
                                    , "@firstName", SqlDbType.VarChar, (null == searchCriteria[Constants.COISearch.FirstName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.FirstName].TrimEnd('*')
                                    , "@lastName", SqlDbType.VarChar, (null == searchCriteria[Constants.COISearch.LastName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.LastName].TrimEnd('*')
                                    , "@supervisorFirstName", SqlDbType.VarChar, (null == searchCriteria[Constants.COISearch.SupervisorFirstName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.SupervisorFirstName].TrimEnd('*')
                                    , "@supervisorLastName", SqlDbType.VarChar, (null == searchCriteria[Constants.COISearch.SupervisorLastName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.SupervisorLastName].TrimEnd('*')
                                    , "@userName", SqlDbType.VarChar, (null == searchCriteria[Constants.COISearch.UserName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.UserName].TrimEnd('*')
                                    , "@isPolicyQuestionSelected", SqlDbType.Bit, (null == searchCriteria[Constants.COISearch.PolicyQuestion]) ? Convert.ToBoolean(0) : Convert.ToBoolean(searchCriteria[Constants.COISearch.PolicyQuestion].TrimEnd('*'))
                                    , "@isNothingToDiscloseSelected", SqlDbType.Bit, (null == searchCriteria[Constants.COISearch.NothingToDisclose]) ? Convert.ToBoolean(0) : Convert.ToBoolean(searchCriteria[Constants.COISearch.NothingToDisclose].TrimEnd('*'))
                                    , "@statusXml", SqlDbType.Text, (null == ds) ? (object)DBNull.Value : ds.GetXml()
                                    , "@recursive", SqlDbType.Bit, (null == searchCriteria[Constants.COISearch.DirectReportsOnly]) ? true : !Convert.ToBoolean(searchCriteria[Constants.COISearch.DirectReportsOnly].TrimEnd('*'))
                                    , "@sortColumn", SqlDbType.VarChar, sortColumn
                                    , "@sortOrder", SqlDbType.Bit, sortAscending
                                    , "@hasExistingRelation", SqlDbType.Bit, (null == searchCriteria[Constants.COISearch.IncludeAll]) ? 0 : 1
                                    , "@HierarchyTypeId", SqlDbType.Int, (null == searchCriteria[Constants.COISearch.HierarchyType]) ? (int)COIUser.HierarchyType.None : Convert.ToInt32(searchCriteria[Constants.COISearch.HierarchyType].Trim())
                                    ));
                            }
                            else
                            {
                                DataContainer = DataAccess.GetDataSet(connectionString, "GetCOIUserHierarchy",
                                   DataAccess.BuildSqlParameterArray("@admPersonId", SqlDbType.Int, (int)args[0]
                                   , "@firstName", SqlDbType.VarChar, (null == searchCriteria[Constants.COISearch.FirstName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.FirstName].TrimEnd('*')
                                   , "@lastName", SqlDbType.VarChar, (null == searchCriteria[Constants.COISearch.LastName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.LastName].TrimEnd('*')
                                   , "@supervisorFirstName", SqlDbType.VarChar, (null == searchCriteria[Constants.COISearch.SupervisorFirstName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.SupervisorFirstName].TrimEnd('*')
                                   , "@supervisorLastName", SqlDbType.VarChar, (null == searchCriteria[Constants.COISearch.SupervisorLastName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.SupervisorLastName].TrimEnd('*')
                                   , "@userName", SqlDbType.VarChar, (null == searchCriteria[Constants.COISearch.UserName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.UserName].TrimEnd('*')
                                   , "@isPolicyQuestionSelected", SqlDbType.Bit, (null == searchCriteria[Constants.COISearch.PolicyQuestion]) ? Convert.ToBoolean(0) : Convert.ToBoolean(searchCriteria[Constants.COISearch.PolicyQuestion].TrimEnd('*'))
                                   , "@isNothingToDiscloseSelected", SqlDbType.Bit, (null == searchCriteria[Constants.COISearch.NothingToDisclose]) ? Convert.ToBoolean(0) : Convert.ToBoolean(searchCriteria[Constants.COISearch.NothingToDisclose].TrimEnd('*'))
                                   , "@statusXml", SqlDbType.Text, (null == ds) ? (object)DBNull.Value : ds.GetXml()
                                   , "@recursive", SqlDbType.Bit, (null == searchCriteria[Constants.COISearch.DirectReportsOnly]) ? true : !Convert.ToBoolean(searchCriteria[Constants.COISearch.DirectReportsOnly].TrimEnd('*'))
                                   , "@sortColumn", SqlDbType.VarChar, sortColumn
                                   , "@sortOrder", SqlDbType.Bit, sortAscending
                                   , "@hasExistingRelation", SqlDbType.Bit, (null == searchCriteria[Constants.COISearch.IncludeAll]) ? 0 : 1
                                   ,"@HierarchyTypeId",SqlDbType.Int,(null == searchCriteria[Constants.COISearch.HierarchyType]) ? (int)COIUser.HierarchyType.Institutional : Convert.ToInt32(searchCriteria[Constants.COISearch.HierarchyType].Trim())
                                   ));
                            }
							break;
						case (int)Constants.SourcePage.HierarchyMaintenenacePage://Hieararchy maintenance page call(To get the list of direct descendants)
                           DataContainer = DataAccess.GetDataSet(connectionString, "GetCOIUserIds",
								DataAccess.BuildSqlParameterArray("@admPersonId", SqlDbType.Int, (int)args[0]
								,"@firstName",SqlDbType.VarChar,(null==searchCriteria[Constants.COISearch.FirstName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.FirstName].TrimEnd('*')
								,"@lastName",SqlDbType.VarChar,(null==searchCriteria[Constants.COISearch.LastName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.LastName].TrimEnd('*')
								,"@userName",SqlDbType.VarChar,(null==searchCriteria[Constants.COISearch.UserName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.UserName].TrimEnd('*')
								,"@recursive",SqlDbType.Bit,0
								,"@sortColumn",SqlDbType.VarChar,sortColumn								
								,"@sortOrder",SqlDbType.Bit,sortAscending
                                , "@HierarchyTypeId", SqlDbType.Int, (null == searchCriteria[Constants.COISearch.HierarchyType]) ? 0 : Convert.ToInt32(searchCriteria[Constants.COISearch.HierarchyType].Trim())
								));
							break;
						case (int)Constants.SourcePage.AddCOIusersPage://Add COI User page call
                        case (int)Constants.SourcePage.AddDelegatesPage:
							DataContainer = DataAccess.GetDataSet(connectionString, "GetUserIds",
								DataAccess.BuildSqlParameterArray(
								"@userName",SqlDbType.VarChar,(null==searchCriteria[Constants.COISearch.UserName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.UserName].TrimEnd('*')
								,"@lastName",SqlDbType.VarChar,(null==searchCriteria[Constants.COISearch.LastName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.LastName].TrimEnd('*')
								,"@firstName",SqlDbType.VarChar,(null==searchCriteria[Constants.COISearch.FirstName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.FirstName].TrimEnd('*')
                                ,"@personTypeId", SqlDbType.VarChar, (null == searchCriteria[Constants.COISearch.ExternalPeople]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.ExternalPeople].ToString()
								,"@sortColumn",SqlDbType.VarChar,sortColumn
								,"@sortOrder",SqlDbType.Bit,sortAscending
								));
							break;
					}						
				}					
				if ( DataContainer.Tables.Count > 0 )
					DataContainer.Tables[0].TableName = IdDataTableName;
				//Set the primary key column for the dataset table
                if ((int)args[1] == (int)Constants.SourcePage.AddCOIusersPage || (int)args[1] == (int)Constants.SourcePage.AddDelegatesPage)
				{
					DataColumn[] dcCollection = new DataColumn[]{DataContainer.Tables[0].Columns["Id"]};
					DataContainer.Tables[0].PrimaryKey=dcCollection;
				}
			}
			catch(Exception ex)
			{
				Utility.ExceptionReport.WriteToEventLog ("Failed to load the COI User Summary,Error occurred while executing the Load()", ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}
		}

        public void LoadDelegates(string connectionString, int supervisorId)
        {
            try
            {
                if (supervisorId > 0)
                {
                    string sortCriteria = string.Format("{0} {1}", SortColumn, sortAscending ? SortSequenceAscending : SortSequenceDescending);

                    DataContainer = DataAccess.GetDataSet(connectionString, "GetDelegateIdsForSupervisor",
                        DataAccess.BuildSqlParameterArray("@supervisorId", SqlDbType.Int, supervisorId
                        , "@sortColumn", SqlDbType.VarChar, sortColumn
                        , "@sortOrder", SqlDbType.Bit, sortAscending
                        ));

                    if (DataContainer.Tables.Count > 0)
                        DataContainer.Tables[0].TableName = IdDataTableName;   
                }
                else
                {
                    DataContainer = null;                   
                }
                            
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to load the COI User Summary,Error occurred while executing the Load()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

		/// <summary>
		/// This load method will be called passing the search criteria(as hashtable) and the userContext via the Params Object[] parameter.
		/// Unlike other objects will not cache the objects, but will handle loading,sorting and paging by caching the COIIds and AdmPersonIds.
		/// </summary>
		/// <param name="connectionString">Database Connection string</param>
		/// <param name="customSearchHash">Hashtable of search parameters</param>
		/// <param name="isIndChart1Checked">determines whether or not the Individual's chart1 search has to be performed </param>
		/// <param name="isIndChart2Checked">determines whether or not the Individual's chart2 search has to be performed</param>
		/// <param name="isIndChart3Checked">determines whether or not the Individual's chart3 search has to be performed</param>
		/// <param name="isOTChart1Checked">determines whether or not the Outside Trsutee's chart1 search has to be performed</param>
		/// <param name="isOTChart2Checked">determines whether or not the Outside Trsutee's chart2 search has to be performed</param>
		public void Load(string connectionString, Hashtable customSearchHash,bool isIndChart1Checked , bool isIndChart2Checked, bool isIndChart3Checked, bool isIndChart4Checked, bool isOTChart1Checked,bool isOTChart2Checked)
		{
			try
			{			
				SqlParameter[] searchArgs;
				int lastIndex=0;
				if (customSearchHash != null) 
				{

				searchArgs=new SqlParameter[customSearchHash.Count+2];

					SqlParameter[] commonSearchArgs=DataAccess.BuildSqlParameterArray(
					"@firstName", SqlDbType.VarChar, customSearchHash["firstName"]
					,"@lastName",SqlDbType.VarChar,customSearchHash["lastName"]
					,"@userName",SqlDbType.VarChar,customSearchHash["userName"]                    
					,"@startDate",SqlDbType.DateTime,customSearchHash["startDate"]
					,"@endDate",SqlDbType.DateTime,customSearchHash["endDate"]					
					,"@roleXml",SqlDbType.Text,(customSearchHash["role"]== DBNull.Value)? (object)DBNull.Value : ConvertToDataSet(customSearchHash["role"].ToString()).GetXml()
					,"@nothingToDisclose",SqlDbType.Bit,customSearchHash["nothingToDisclose"]
                   	,"@outsideEntityName",SqlDbType.VarChar,customSearchHash["outsideEntityName"]
					,"@businessNature" ,SqlDbType.VarChar,customSearchHash["businessNature"]
					,"@companyType" ,SqlDbType.VarChar,customSearchHash["companyType"]
					,"@percentageTotalEquityOwned" ,SqlDbType.VarChar,customSearchHash["percentageTotalEquityOwned"]
					);
					commonSearchArgs.CopyTo(searchArgs,lastIndex);
					lastIndex=lastIndex + commonSearchArgs.Length;
				//Individual Chart1 search parameters
				if (isIndChart1Checked)	
					{
					
					SqlParameter[] chart1searchArgs=DataAccess.BuildSqlParameterArray(
						"@chart1ServiceProvided",SqlDbType.VarChar,customSearchHash["serviceProvided"]
						,"@chart1AnnualEquityCompensation",SqlDbType.VarChar,customSearchHash["annualEquityCompensation"]
						,"@chart1FiduciaryPosition",SqlDbType.VarChar,customSearchHash["fiduciaryPosition"]
						,"@chart1ValueOfEquityOwned",SqlDbType.VarChar,customSearchHash["valueOfEquityOwned"]
						,"@chart1WrittenAgreement",SqlDbType.VarChar,customSearchHash["writtenAgreement"]
						,"@chart1DaysOfOutsideActivity",SqlDbType.VarChar,customSearchHash["daysOfOutsideActivity"]
						,"@chart1IsAggregate",SqlDbType.Bit,customSearchHash["isAggregateEquity"]
						,"@chart1AnnualCashCompensation",SqlDbType.VarChar,customSearchHash["annualCashCompensation"]
                        ,"@chart1CSRLReview", SqlDbType.VarChar, customSearchHash["csrlReview"]
						);
											
					chart1searchArgs.CopyTo(searchArgs,lastIndex);	
					lastIndex=lastIndex + chart1searchArgs.Length;
					}
					//Individual Chart2 search parameters
					if (isIndChart2Checked)	
					{
						SqlParameter[] chart2searchArgs=DataAccess.BuildSqlParameterArray(
						"@chart2ValueOfEquityInterest",SqlDbType.VarChar,customSearchHash["chart2ValueOfEquityInterest"]												
							);
						chart2searchArgs.CopyTo(searchArgs,lastIndex);
						lastIndex=lastIndex + chart2searchArgs.Length;
					}
					//Individual Chart3 search parameters
					if (isIndChart3Checked)	
					{
						SqlParameter[] chart3searchArgs=DataAccess.BuildSqlParameterArray(
							"@chart3FamilyMember",SqlDbType.VarChar,customSearchHash["familyMember"]
							,"@chart3FamilyMemberAssociation",SqlDbType.VarChar,customSearchHash["familyMemberAssociation"]
							,"@chart3ValueOfEquityInterest",SqlDbType.VarChar,customSearchHash["chart3ValueOfEquityInterest"]
							);
						chart3searchArgs.CopyTo(searchArgs,lastIndex);
						lastIndex=lastIndex + chart3searchArgs.Length;
					}
                    //Individual Chart3 search parameters
                    if (isIndChart4Checked)
                    {
                        SqlParameter[] chart4searchArgs = DataAccess.BuildSqlParameterArray(
                            "@chart4isPayedBypartners", SqlDbType.VarChar, customSearchHash["isPayedByPartners"]
                            , "@chart4paymentNature", SqlDbType.VarChar, customSearchHash["paymentNature"]
                            , "@chart4paymentPurpose", SqlDbType.VarChar, customSearchHash["paymentPurpose"]
                            , "@chart4annualPayment", SqlDbType.VarChar, customSearchHash["annualPayment"]
                            );
                        chart4searchArgs.CopyTo(searchArgs, lastIndex);
                        lastIndex = lastIndex + chart4searchArgs.Length;
                    }
					//Outside trustee Chart1 search parameters
					if (isOTChart1Checked)	
					{						
						SqlParameter[] trusteeChart1searchArgs=DataAccess.BuildSqlParameterArray(
							"@trusteeNonPartnersAssociationChart1",SqlDbType.VarChar,customSearchHash["nonPartnerAssociation"]
							,"@trusteeDescOfOutsideEntityChart1",SqlDbType.VarChar,customSearchHash["oMdescOfOutsideEntity"]
							);
						trusteeChart1searchArgs.CopyTo(searchArgs,lastIndex);
						lastIndex=lastIndex + trusteeChart1searchArgs.Length;
					}
					//Outside trustee Chart2 search parameters
					if (isOTChart2Checked)	
					{
						SqlParameter[] trusteeChart2searchArgs=DataAccess.BuildSqlParameterArray(	
							"@trusteeFamilyMemberChart2",SqlDbType.VarChar,customSearchHash["oTFamilyMember"]																	
							);
						trusteeChart2searchArgs.CopyTo(searchArgs,lastIndex);
						lastIndex=lastIndex + trusteeChart2searchArgs.Length;
					}						
					SqlParameter[] sortParameterArgs=DataAccess.BuildSqlParameterArray(	
						"@sortColumn",SqlDbType.VarChar,sortColumn
						,"@sortOrder",SqlDbType.Bit,sortAscending
						,"@selectedChartXml",SqlDbType.Text,(customSearchHash["selectedCharts"]== DBNull.Value)? (object)DBNull.Value : ConvertToDataSet(customSearchHash["selectedCharts"].ToString()).GetXml()
						);
					sortParameterArgs.CopyTo(searchArgs,lastIndex);
					DataContainer = DataAccess.GetDataSet(connectionString, "GetCustomReport",searchArgs);
				}		
			
				if ( DataContainer.Tables.Count == 1 )
					DataContainer.Tables[0].TableName = IdDataTableName;
			}
			catch(Exception ex)
			{
				Utility.ExceptionReport.WriteToEventLog ("Failed to load the COI User Summary,Error occurred while executing the Load()", ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}			
		}

		/// <summary>
		/// This method fetches the details that will be bound to the grid .
		/// Note:The required Ids will be fetched from the cached Ids using the start index and the no. of ids to be fetched.
		///		These selected Ids(datatable) will then be added to the dataset to generate a xml object that will be consumed by the stored procedure
		/// </summary>
		/// <param name="startIndex"> The stating index of the record to be fetched</param>
		/// <param name="count">Number of records to fetched from the starting index</param>
		/// <param name="connectionString">Database Connection string.</param>
		/// <param name="args">Reference page index stating from which page,the call has been made.Note:Different stored procedures will be called for each reference index</param>
		/// <returns>DataTable(Details) to be bound to the datagrid</returns>
		public DataTable GetSummaryList(int startIndex,int count,string connectionString,params object[] args)
		{
			DataTable idTable=null;
			try
			{
				if (args.Length !=1)
				{
					throw new Exception("COIUserSummary.GetSummaryList requires reference page index");
				}
				idTable=DataContainer.Tables[IdDataTableName]; 
				DataTable dt=idTable.Clone(); 
				int rowCount=idTable.Rows.Count;
				
				for (int i = startIndex < 0 ? 0: startIndex, end = Math.Min(i + count,rowCount); i < end; ++i)
				{
					dt.ImportRow(idTable.Rows[i]);
				}	

				DataSet ds = new DataSet();
				ds.Tables.Add(dt);

					string sPToExec="GetCOIUserHierarchyList";
					
					switch ((int)args[0])
					{
                        case (int)Constants.SourcePage.HierarchyMaintenenacePage:
						{
							sPToExec="GetCOIUserList";
							break;
						}
                        case (int)Constants.SourcePage.AddCOIusersPage:
                        case (int)Constants.SourcePage.AddDelegatesPage:
						{
							sPToExec="GetUsersById";							
							break;
						}						
						case (int)Constants.SourcePage.CustomReportsPage:
						{
							sPToExec="GetCustomReportList";							
							break;
						}
                        case (int)Constants.SourcePage.DelegateManagementPage:
                        {
                            sPToExec = "GetDelegateList";
                            break;
                        }
						default:
							break;
					}		
					return DataAccess.GetDataTable(
						connectionString, sPToExec ,DataAccess.BuildSqlParameterArray(
						"@xml", SqlDbType.Text, ds.GetXml()));
			}
			catch(Exception ex)
			{
				Utility.ExceptionReport.WriteToEventLog ("Failed to get the Summary List ,Error occurred while executing the COIUserSummary.GetSummaryList()", ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}			
		}

		/// <summary>
		/// This method fetches the details that will be bound to the grid(Selected users grid AddCOIUsers page.)
		/// The user ids selected to be added to the list from the search results grid will be cached in the dataTable,
		/// which in turn will be converted to XMl object to be consumed by the stored procedure.
		/// </summary>
		/// <param name="connectionString">Database connection string</param>
		/// <returns>Datatable(details)</returns>
		public DataTable GetSelectedUsersSummary(string connectionString)
		{
			try
			{
				DataSet ds=new DataSet();
				if (selectedIdsTable != null && selectedIdsTable.Rows.Count>0)
				{
					DataTable dt=selectedIdsTable.Clone();
					foreach (DataRow dr in selectedIdsTable.Rows)
							dt.ImportRow(dr);
					ds.Tables.Add(dt);

					return DataAccess.GetDataTable(
						connectionString, "GetUsersById" ,DataAccess.BuildSqlParameterArray(
						"@xml", SqlDbType.Text, ds.GetXml()
						,"@referringTable",SqlDbType.Int,1));
				}
				else
				{
					return null;
				}
							
			}
			catch(Exception ex)
			{
				Utility.ExceptionReport.WriteToEventLog ("Failed to get the Summary List ,Error occurred while executing the COIUserSummary.GetSummaryList()", ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}			
		}

		/// <summary>
		/// This method will be initiated by the "Review All Pages/Review Selected" action in the supervisor summary page and will update the following 
		/// 1.Status of the COI to 'Reviewed' 
		/// 2.DateReviewed to 'CurrentDate' 
		/// </summary>
		/// <param name="connectionString"></param>
        public void UpdateReviewDate(string connectionString, int reviewerPersonId, params object[] args)
		{
			try
			{
                DataTable idTable = DataContainer.Tables[IdDataTableName];
                DataSet ds = new DataSet(); 
                if (args.Length > 0)
                {
                    DataTable dt = idTable.Clone();
                    ArrayList reveiwedCOIs = (ArrayList)args[0];
                    for (int i = 0; i < reveiwedCOIs.Count; i++)
                    {
                        foreach (DataRow dr in idTable.Rows)
                        {
                            if (dr["COIId"].ToString() == reveiwedCOIs[i].ToString())
                            {
                                dt.ImportRow(dr);
                                break;
                            }
                        }
                    }
                   ds.Tables.Add(dt);
                }
                    
                    DataAccess.ExecuteNonQuery(connectionString, "UpdateReviewInfo", DataAccess.BuildSqlParameterArray(
                        "@xml", SqlDbType.Text, (args.Length > 0) ? ds.GetXml() : DataContainer.GetXml()
                        ,"@reviewerPersonId", SqlDbType.Int, reviewerPersonId
                        ,"@skipReviewerCOI",SqlDbType.Bit, 1 //By default the reviewer cannot review his/her own COI
                        ));
                    //}	
                
			}
			catch(Exception ex)
			{
				Utility.ExceptionReport.WriteToEventLog ("Failed to Update the review date ,Error occurred while executing the COIUserSummary.UpdateReviewDate()", ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}
		}

        /// <summary>
        /// This method will update the following for the COIId passed in.
        /// 1.Status of the COI to 'Reviewed' 
        /// 2.DateReviewed to 'CurrentDate' 
        /// </summary>
        /// <param name="connectionString"></param>
        public static void ReviewCOI(string connectionString, int coiId, int reviwerPersonId)
        {
            try
            {
                if (coiId > 0)
                {
                    DataAccess.ExecuteNonQuery(connectionString, "ReviewCOI", DataAccess.BuildSqlParameterArray(
                    "@coiId",SqlDbType.Int,coiId,
                    "@reviewerPersonId", SqlDbType.Int, reviwerPersonId));
                } 
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to review COI ,Error occurred while trying to review COI from COIUserSummary.ReviewCOI()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        /// <summary>
        /// This method will be initiated by the "Review All Pages/Review Selected" action in the supervisor summary page and will update the following 
        /// 1.Status of the COI to 'Reviewed' 
        /// 2.DateReviewed to 'CurrentDate' 
        /// </summary>
        /// <param name="connectionString"></param>
        public void RemoveDelegate(string connectionString, params object[] args)
        {
            try
            {
                DataTable idTable = DataContainer.Tables[IdDataTableName];
                DataSet ds = new DataSet();
                if (args.Length > 0)
                {
                    DataTable dt = idTable.Clone();
                    ArrayList delegatesForRemovals = (ArrayList)args[0];
                    for (int i = 0; i < delegatesForRemovals.Count; i++)
                    {
                        foreach (DataRow dr in idTable.Rows)
                        {
                            if (dr["Id"].ToString() == delegatesForRemovals[i].ToString())
                            {
                                dt.ImportRow(dr);
                                break;
                            }
                        }
                    }
                    ds.Tables.Add(dt);
                }

                DataAccess.ExecuteNonQuery(connectionString, "RemoveDelegateForSupervisor", DataAccess.BuildSqlParameterArray(
                    "@xml", SqlDbType.Text, (args.Length > 0) ? ds.GetXml() : DataContainer.GetXml()
                    ,"@supervisorId", SqlDbType.Int, (int)args[1]));
                //}	

            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to Update the review date ,Error occurred while executing the COIUserSummary.UpdateReviewDate()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

       	/// <summary>
		/// Removes the selected users from the search results grid and adds it to the selecetd users list(to be added to the hierarchy from the search results)
		/// Note:The UserId collection for the two grids available in the AddCOIUsers page will be cached in two different tables.
		/// </summary>
		/// <param name="personId">admPersonId to be removed from the initial search results list</param>
		public void AddSelectedUserToList(int personId)
		{
			try
			{
				DataTable idTable=DataContainer.Tables[IdDataTableName];
				if (idTable.Rows.Count>0 )
				{
					if (selectedIdsTable==null)
					{
						selectedIdsTable=idTable.Clone();
						selectedIdsTable.TableName="selectedIdsTable";						
					}
					DataRow dr=idTable.Rows.Find(personId);
					idTable.Rows.Remove(dr);
					
					DataRow drSelected=selectedIdsTable.NewRow();
					drSelected["Id"]=personId;
					selectedIdsTable.Rows.Add(drSelected);				

				}				
			}
			catch(Exception ex)
			{
				Utility.ExceptionReport.WriteToEventLog ("Failed while tring to remove the selected users from the list ,Error occurred while executing the COIUserSummary.RemoveSelectedUserId()", ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}
		}
		#endregion

		#region public static methods
		/// <summary>
		/// Gets the COI with 'Start' or 'InProgress' status matching the passed in admpersonId.
		/// </summary>
		/// <param name="connectionString">database connection string</param>
		/// <param name="admPersonId">PersonId to search for</param>
		/// <returns>datarow of matching COI</returns>
		public static DataRow GetCOIInProgress(string connectionString,int admPersonId)
		{
			DataRow cOIdatRow=null;
			try
			{
				if (admPersonId !=0)
				{
					 DataTable dt=DataAccess.GetDataTable(
						connectionString, "GetCOIInProgress" ,DataAccess.BuildSqlParameterArray(
						"@admPersonId", SqlDbType.Int, admPersonId));

					if ((dt !=null)&&(dt.Rows.Count==1))
					{
						cOIdatRow =dt.Rows[0];						
					}
				}
			}
			catch(Exception ex)
			{
				Utility.ExceptionReport.WriteToEventLog ("Failed to get the InProgress COI,Error occurred while executing the COIUserSummary.GetCOIInProgress()", ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}
			return cOIdatRow;
		}
		

		/// <summary>
		/// Adds the list of selected users from the add user page (from selected users grid)to hierarchy.
		/// </summary>
		/// <param name="ds">dataset containing the parent and child Ids</param>
		public static void AddUserHierarchyMap (string connectionString,DataSet ds)
		{
			try
			{
				if (ds !=null)
				{
					DataAccess.ExecuteNonQuery(
						connectionString, "AddCOIUserHierarchyMap" ,DataAccess.BuildSqlParameterArray(
						"@xml", SqlDbType.Text, ds.GetXml()));					
				}
			}
			catch(Exception ex)
			{
				Utility.ExceptionReport.WriteToEventLog ("Failed to add selected users to COI hiererachy,Error occurred while executing the COIUserSummary.AddUserHierarchyMap()", ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}
		}

        /// <summary>
        /// Adds the list of selected users from the add user page (from selected users grid)to hierarchy.
        /// </summary>
        /// <param name="ds">dataset containing the parent and child Ids</param>
        public static void AddDelegateMap(string connectionString, DataSet ds)
        {
            try
            {
                if (ds != null)
                {
                    DataAccess.ExecuteNonQuery(
                        connectionString, "AddDelegateMap", DataAccess.BuildSqlParameterArray(
                        "@xml", SqlDbType.Text, ds.GetXml()));
                }
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to add selected user(s) as delegate(s),Error occurred while executing the COIUserSummary.AddDelegateMap()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }
		/// <summary>
		/// This methods removes the parent child relation for the selected users.
		/// Note:Does not remove the person physically, but flips the status to removed(1).
		/// </summary>
		/// <param name="parentId">parentId associated</param>
		/// <param name="childId">childId associated</param>
		public static void RemoveUserFromHierarchy(string connectionString,int parentId,int childId,int HierarchyTypeId)
		{
			try
			{
				if (parentId != 0 && childId != 0)
				{
					DataAccess.ExecuteNonQuery(
						connectionString, "RemoveCOIUserHierarchyMap" ,DataAccess.BuildSqlParameterArray(
						"@parentId", SqlDbType.Int, parentId
						,"@childId",SqlDbType.Int, childId
                        , "@HierarchyTypeId", SqlDbType.Int, HierarchyTypeId));					
				}
			}
			catch(Exception ex)
			{
				Utility.ExceptionReport.WriteToEventLog ("Failed to remove the mapping,Error occurred while executing the COIUserSummary.RemovePersonFromHierarchy()", ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}
		}

		/// <summary>
		/// Gets the users with supervisor and reviewer access rights performing a system wide search.
		/// Usage:To Impersonate a person.
		/// </summary>
		/// <param name="connectionString">database connection string</param>
		/// <param name="admPersonId">personId to search for</param>
		/// <returns>datarow of matching COI</returns>
		public static DataTable GetSupervisorsForImpersonation(string connectionString,int admPersonId,SearchCriteria searchCriteria,bool isPostBack)
		{
			DataTable dt=null;
			try
			{
				if (admPersonId !=0)
				{
					return DataAccess.GetDataTable(
						connectionString, "GetSupervisorsForImpersonation" ,DataAccess.BuildSqlParameterArray(
						"@admPersonId", SqlDbType.Int, admPersonId
						,"@firstName",SqlDbType.VarChar,(!isPostBack)?(object)DBNull.Value:(null==searchCriteria[Constants.COISearch.FirstName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.FirstName].TrimEnd('*')
						,"@lastName",SqlDbType.VarChar,(!isPostBack)?(object)DBNull.Value:(null==searchCriteria[Constants.COISearch.LastName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.LastName].TrimEnd('*')
						,"@userName",SqlDbType.VarChar,(!isPostBack)?(object)DBNull.Value:(null==searchCriteria[Constants.COISearch.UserName]) ? (object)DBNull.Value : searchCriteria[Constants.COISearch.UserName].TrimEnd('*')
						,"@isSystemWide",SqlDbType.Bit,1
						,"@isRecursive",SqlDbType.Bit,1
                        ,"@HierarchyTypeId", SqlDbType.Int, (null == searchCriteria[Constants.COISearch.HierarchyType]) ? (int)COIUser.HierarchyType.None : Convert.ToInt32(searchCriteria[Constants.COISearch.HierarchyType].Trim())
						));				
				}
			}
			catch(Exception ex)
			{
				Utility.ExceptionReport.WriteToEventLog ("Failed to get the supervisor and reviewers from the system,Error occurred while executing the COIUserSummary.GetSupervisorAndReviwerFromHierarchy()", ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}
			return dt;
		}

		/// <summary> 
		/// 1. Notification type = 
		/// a. Open Enrollment: Adds an Start COI to all the users who have access to COI and who do not have
		///	 a Start or an In Progress COI. For users with existing Start/InProgress COI, the last notified
		///	 date is updated. 
		/// b. Reminder/Overdue: Updates the last notified date for all existing Start/InProgress COIs 
		/// </summary>
		/// <param name="connectionString">Information required to connect to the database</param>
		/// <param name="notificationType">Type of the notification</param>
		/// <returns></returns>
		public static DataSet ExecuteNotification(string connectionString, string notificationType, int institutionId, DateTime lastActivityDate, int reportingPeriodId)
		{
			try
			{
				DataSet ds = DataAccess.GetDataSet(
					connectionString, "GetCOIUsersForNotification", DataAccess.BuildSqlParameterArray(
					"@type", SqlDbType.VarChar, notificationType
					, "@institutionId", SqlDbType.Int, institutionId
					, "@lastActivityDate", SqlDbType.DateTime, (lastActivityDate == DateTime.MinValue) ? (object)DBNull.Value : lastActivityDate
					, "@reportingPeriodId", SqlDbType.Int, reportingPeriodId));

				if (ds != null && ds.Tables.Count > 0)
				{
					ds.Tables[0].TableName = "ValidEmailList";
					if (ds.Tables.Count == 2)
						ds.Tables[1].TableName = "InvalidEmailList";
				}

				return ds;
			}
			catch (Exception ex)
			{
				Utility.ExceptionReport.WriteToEventLog("COIUserSummary.ExecuteNotification(): Failed to execute the notification.",
					ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}
		}
		/// <summary>
        /// Gets the institution or department or unit based on the institutionId and departmentId passed in as parameter
        /// Not being used as institution,Department and Unit are pulled out of the custom reports which are replaced by Organization.
        /// </summary>
        /// <param name="institutionId">Institution Id to which the departments has to be retrieved </param>
        /// <param name="departmentId">department id to which the unit has to be retrieved</param>
        /// <returns>Datatable</returns>
        public static DataTable GetInstitutionOrUnitOrDepartment(string connectionString, int institutionId, int departmentId)
        {
            try
            {
                return DataAccess.GetDataTable(
                    connectionString, "GetInstitutionOrUnitOrDept", DataAccess.BuildSqlParameterArray(
                    "@institutionId", SqlDbType.Int, institutionId
                    , "@departmentId", SqlDbType.Int, departmentId));

            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("COIUserSummary.GetInstitutionOrUnitOrDepartment(): Failed to execute the Institution/Department/Unit.",
                    ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

		/// <summary>
		/// Gets the institution or department or unit based on the institutionId and departmentId passed in as parameter
		/// Not being used as institution,Department and Unit are pulled out of the custom reports which are replaced by Organization.
		/// </summary>
		/// <param name="institutionId">Institution Id to which the departments has to be retrieved </param>
		/// <param name="departmentId">department id to which the unit has to be retrieved</param>
		/// <returns>Datatable</returns>
		public static DataTable GetInstitutionOrUnitDepartment(string connectionString,int institutionId )
		{
			try
			{				
					return DataAccess.GetDataTable(
						connectionString, "GetInstitutionOrUnitDept" ,DataAccess.BuildSqlParameterArray(
						"@institutionId", SqlDbType.Int, institutionId));					
				
			}
			catch(Exception ex)
			{
                Utility.ExceptionReport.WriteToEventLog("COIUserSummary.GetInstitutionOrUnitDepartment(): Failed while executing GetInstitutionOrUnitDept procedure.", 
					ex, true, Utility.ExceptionReport.ReportType.Error);
				throw;
			}
		}

        /// <summary>
        /// Gets the users with supervisor and reviewer access rights performing a system wide search.
        /// Usage:To Impersonate a person.
        /// </summary>
        /// <param name="connectionString">database connection string</param>
        /// <param name="admPersonId">personId to search for</param>
        /// <returns>datarow of matching COI</returns>
        public static DataTable GetSupervisorsByOrganization(string connectionString, int organizationId, string unitDept, string lastName,string firstName, string userName)
        {
            DataTable dt = null;
            try
            {
                 dt =  DataAccess.GetDataTable(
                        connectionString, "GetSupervisorsByOrganization", DataAccess.BuildSqlParameterArray(
                        "@organizationId", SqlDbType.Int, organizationId
                        , "@unitDept", SqlDbType.VarChar, (unitDept == string.Empty) ? (object)DBNull.Value : unitDept
                        , "@firstName", SqlDbType.VarChar, (string.Empty == firstName)? (object)DBNull.Value : firstName
                        , "@lastName", SqlDbType.VarChar, (string.Empty == lastName) ? (object)DBNull.Value : lastName
                        , "@userName", SqlDbType.VarChar, (string.Empty == userName) ? (object)DBNull.Value : userName
                        ));                
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the supervisor by organization, Error occurred while executing the COIUserSummary.GetSupervisorsByOrganization()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
            return dt;
        }
        /// <summary>
        /// Determine whether or not the logged in user is a delegate
        /// </summary>
        /// <param name="userId">logged in User Id</param>
        /// <returns>boolean value representing denoting the delegate status of the user</returns>
        public static bool IsLoggedInUserADelegate(int userId, string connectionString)
        {
                try
                {
                      return Convert.ToBoolean(DataAccess.ExecuteScalar(connectionString, "CheckDelegateStatus", DataAccess.BuildSqlParameterArray(
                            "@delegateId", SqlDbType.Int, userId)));                  
                    
                }
                catch (Exception ex)
                {
                    Utility.ExceptionReport.WriteToEventLog("Failed to get the delegate status of the logged-in user, Error occurred while executing the COIUserSummary.IsLoggedInUserADelegate()", ex, true, Utility.ExceptionReport.ReportType.Error);
                    throw;
                }
           
        }


        /// <summary>
        /// Gets the COIReview Note for the passes in COIId
        /// </summary>
        /// <param name="userId">COIId for which the review notes have to be obtained</param>
        /// <returns>sqlDatareader containing the review note and the review related details.</returns>
        public static SqlDataReader GetCOIReviewNote(int coiId, string connectionString)
        {
            SqlDataReader coiReviewNoteReader = null;
            try
            {                
                coiReviewNoteReader = DataAccess.ExecuteReader(connectionString, "GetCOIReviewNote", DataAccess.BuildSqlParameterArray(
                      "@coiId", SqlDbType.Int, coiId));

            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the COI Review Note status, Error occurred while executing the COIUserSummary.GetCOIReviewNote()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to retrieve COI Review note");
            }
            return coiReviewNoteReader;
        }

        /// <summary>
        /// Saves the review note entered by the user(supervisor/Delegate)
        /// </summary>
        /// <param name="coiId">CoiId for which the review note has to be added</param>
        /// <returns>boolean value representing denoting the delegate status of the user</returns>
        public static void SaveCOIReviewNote(int coiId, string reviewNote, string connectionString)
        {
            try
            {
                DataAccess.ExecuteNonQuery(connectionString, "SaveCOIReviewNote", DataAccess.BuildSqlParameterArray(
                      "@coiId", SqlDbType.Int, coiId,
                      "@reviewNote",SqlDbType.VarChar, reviewNote));

            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Save COI review Note status, Error occurred while executing the COIUserSummary.SaveCOIReviewNote()", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to save COI Review note");
            }

        }


        /// <summary>
        /// Returns all the COI users starting with the specified name
        /// </summary>
        /// <returns>A table holding the search results with admPerson Id and Name (Firstname and Lastname in a single field.)</returns>
        public static DataTable GetCOIUsersForDisclosure(int intAdmpersonId, string strFirstName, string strLastName, string strUserName, string strSupervisorFirstName, string strSupervisorLastName)
        {
            try
            {
                return DataAccess.GetDataTable(Utility.DataAccess.ConnectionString, "GetCOIUsersForDisclosure",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@admPersonId", SqlDbType.Int, intAdmpersonId,
                        "@firstName", SqlDbType.VarChar, (string.Empty == strFirstName) ? (object)DBNull.Value : strFirstName,
                        "@lastName", SqlDbType.VarChar, (string.Empty == strLastName) ? (object)DBNull.Value : strLastName,
                        "@userName", SqlDbType.VarChar, (string.Empty == strUserName) ? (object)DBNull.Value : strUserName,
                        "@supervisorFirstName", SqlDbType.VarChar, (string.Empty == strSupervisorFirstName) ? (object)DBNull.Value : strSupervisorFirstName,
                        "@supervisorLastName", SqlDbType.VarChar, (string.Empty == strSupervisorLastName) ? (object)DBNull.Value : strSupervisorLastName
                        ));
            }
            catch (Exception e)
            {
                throw new Exception("Failed to load GetUsersForDisclosure based on the search criteria.", e);
            }
        }


        /// <summary>
        /// Returns all the Disclosures for COI users based on admPersonIDs and COI Organization Ids
        /// </summary>
        /// <returns>A table with COI Disclosures for the users.</returns>
        public static DataTable GetDisclosuresForCOIUsers(string strAdmPersonIds, string strCOIOrgIds,string strOrderBy,int intPageSize,int intPageStartIndex,int intPageEndIndex)
        {
            try
            {
                return DataAccess.GetDataTable(Utility.DataAccess.ConnectionString, "GetDisclosuresByCOIOrgAdmPersonID",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@admPersonIds", SqlDbType.VarChar, (string.Empty == strAdmPersonIds) ? (object)DBNull.Value : strAdmPersonIds,
                        "@COIOrgIds", SqlDbType.VarChar, (string.Empty == strCOIOrgIds) ? (object)DBNull.Value : strCOIOrgIds,
                        "@OrderBy", SqlDbType.VarChar, (string.Empty == strOrderBy) ? (object)DBNull.Value : strOrderBy,
                        "@PageSize", SqlDbType.Int, intPageSize,
                        "@PageStartIndex", SqlDbType.Int, intPageStartIndex,
                        "@PageEndIndex", SqlDbType.Int, intPageEndIndex
                        ));
            }
            catch (Exception e)
            {
                throw new Exception("Failed to load GetDisclosuresByCOIOrgAdmPersonID based on the search criteria.", e);
            }
        }

		#endregion		

      
	}
}
